/*
 * Copyright 2014 Cungmua, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package cungmua.vn.kiosk.db;

import java.util.ArrayList;
import java.util.List;

import cungmua.vn.kiosk.model.DonHang;
import cungmua.vn.kiosk.model.NhanVien;
import cungmua.vn.kiosk.model.SKU;
import cungmua.vn.kiosk.utils.Constant;
import cungmua.vn.kiosk.utils.ConvertUnsigned;
import cungmua.vn.kiosk.utils.Utils;
import cungmua.vn.kiosk.utils.WriteLog;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author kelvin.nguyen
 * 
 */

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "kiosk_database";

	// Account table name
	private static final String TABLE_ACCOUNT = "tbl_account";

	// DonHang table name
	private static final String TABLE_DONHANG = "tbl_donhang";

	// SKU table name
	private static final String TABLE_SKU = "tbl_sku";

	// Accounts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_MOBILE = "mobile";
	private static final String KEY_IMAGEURL = "imageUrl";
	private static final String KEY_TOKEN = "token";

	// Don Hang Table Columns names
	private static final String KEY_DH_ORDERID = "orderId";
	private static final String KEY_DH_ADDRESS = "address";
	private static final String KEY_DH_BUILDINGNAME = "buildingName";
	private static final String KEY_DH_CUSTOMERNAME = "customerName";
	private static final String KEY_DH_PHONE = "phone";
	private static final String KEY_DH_ADDRESSTYPE = "addressType";
	private static final String KEY_DH_NOTE = "Note";
	private static final String KEY_DH_TOTALMONEY = "totalMoney";
	private static final String KEY_DH_TIMECREATED = "timeCreated”";
	private static final String KEY_DH_HISTORYLOG = "historyLog";
	private static final String KEY_DH_SHIPEXPRESS = "shipExpress";
	private static final String KEY_DH_STATUS = "status";
	private static final String KEY_DH_STATUS_TRAVE = "status_trave";
	private static final String KEY_DH_ISVIP = "IsVip";
	private static final String KEY_DH_NUMBERDELAY = "numberDelay";
	private static final String KEY_DH_IS_SYNCHONIZE="is_Synchonize";

	// SKU Table Columns names
	private static final String KEY_SKU_ID = "_id";
	private static final String KEY_SKU_ORDERID = "orderId";
	private static final String KEY_SKU_SKUID = "SkuId";
	private static final String KEY_SKU_SKUNAME = "SkuName";
	private static final String KEY_SKU_PRICE = "Price";
	private static final String KEY_SKU_QUANTITY = "Quantity";
	private static final String KEY_SKU_PRODUCTTYPE = "ProductType";
	private static final String KEY_SKU_STATUS = "sku_status";
	private static final String KEY_SKU_QUANTITY_DELIVERY = "Quantity_Delivery";
	private static final String KEY_SKU_WHY_CHANGE = "why_change";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_ACCOUNTS_TABLE = "CREATE TABLE " + TABLE_ACCOUNT + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT, "
				+ KEY_MOBILE + " INTEGER, " + KEY_IMAGEURL + " TEXT, "
				+ KEY_TOKEN + " TEXT" + ")";

		String CREATE_DONHANGS_TABLE = "CREATE TABLE " + TABLE_DONHANG + "("
				+ KEY_DH_ORDERID + " INTEGER PRIMARY KEY," + KEY_DH_ADDRESS
				+ " TEXT, " + KEY_DH_BUILDINGNAME + " TEXT, "
				+ KEY_DH_CUSTOMERNAME + " TEXT, " + KEY_DH_PHONE + " TEXT, "
				+ KEY_DH_TOTALMONEY + " INTEGER, " + KEY_DH_TIMECREATED
				+ " TEXT, " + KEY_DH_HISTORYLOG + " TEXT, "
				+ KEY_DH_SHIPEXPRESS + " TEXT, " + KEY_DH_ADDRESSTYPE
				+ " INTEGER, " + KEY_DH_NOTE + " TEXT, " + KEY_DH_STATUS
				+ " INTEGER, " + KEY_DH_STATUS_TRAVE + " INTEGER, "
				+ KEY_DH_ISVIP + " INTEGER, " + KEY_DH_NUMBERDELAY
				+ " INTEGER,  "+ KEY_DH_IS_SYNCHONIZE + " INTEGER" + ")";

		String CREATE_SKU_TABLE = "CREATE TABLE " + TABLE_SKU + "("
				+ KEY_SKU_ID + " integer primary key AUTOINCREMENT, "
				+ KEY_SKU_ORDERID + " INTEGER ," + KEY_SKU_SKUID + " INTEGER,"
				+ KEY_SKU_SKUNAME + " TEXT, " + KEY_SKU_PRICE + " double, "
				+ KEY_SKU_QUANTITY + " TEXT, " + KEY_SKU_PRODUCTTYPE
				+ " INTEGER, " + KEY_SKU_STATUS + " INTEGER, "
				+ KEY_SKU_QUANTITY_DELIVERY + " INTEGER, " + KEY_SKU_WHY_CHANGE
				+ " INTEGER" + ")";

		db.execSQL(CREATE_ACCOUNTS_TABLE);
		db.execSQL(CREATE_DONHANGS_TABLE);
		db.execSQL(CREATE_SKU_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCOUNT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_DONHANG);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SKU);

		// Create tables again
		onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new nhanvien
	public void addNhanvien(NhanVien nhanvien) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ID, nhanvien.getId());// account id
		values.put(KEY_NAME, nhanvien.getName()); // account Name
		values.put(KEY_MOBILE, nhanvien.getMobile()); // account Phone
		values.put(KEY_IMAGEURL, nhanvien.getImageUrl()); // account avatar
		values.put(KEY_TOKEN, nhanvien.getToken()); // account Token

		// Inserting Row
		db.insert(TABLE_ACCOUNT, null, values);
		db.close(); // Closing database connection
	}

	// Getting single nhan vien
	public NhanVien getNhanVien(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_ACCOUNT, new String[] { KEY_ID,
				KEY_NAME, KEY_MOBILE, KEY_IMAGEURL, KEY_TOKEN }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		NhanVien nhanvien = new NhanVien(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), Integer.parseInt(cursor.getString(2)),
				cursor.getString(3), cursor.getString(4));
		// return nhanvien
		return nhanvien;
	}

	// Getting All Nhanviens
	public List<NhanVien> getAllNhanviens() {
		List<NhanVien> nhanvienList = new ArrayList<NhanVien>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_ACCOUNT;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				NhanVien nv = new NhanVien();
				nv.setId(Integer.parseInt(cursor.getString(0)));
				nv.setName(cursor.getString(1));
				nv.setMobile(Integer.parseInt(cursor.getString(2)));
				nv.setImageUrl(cursor.getString(3));
				nv.setToken(cursor.getString(4));

				// Adding nhanvien to list
				nhanvienList.add(nv);
			} while (cursor.moveToNext());
		}

		// return nhanvien list
		return nhanvienList;
	}

	// Updating single nhanvien
	public int updateNhanvien(NhanVien nhanvien) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, nhanvien.getName()); // account Name
		values.put(KEY_MOBILE, nhanvien.getMobile()); // account Phone
		values.put(KEY_IMAGEURL, nhanvien.getImageUrl()); // account avatar
		values.put(KEY_TOKEN, nhanvien.getToken()); // account Token

		// updating row
		return db.update(TABLE_ACCOUNT, values, KEY_ID + " = ?",
				new String[] { String.valueOf(nhanvien.getId()) });
	}

	// Deleting single nhanvien
	public void deleteNhanvien(NhanVien nhanvien) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_ACCOUNT, KEY_ID + " = ?",
				new String[] { String.valueOf(nhanvien.getId()) });
		db.close();
	}

	// Getting nhanviens Count
	public int getNhanvienCount() {
		String countQuery = "SELECT  * FROM " + TABLE_ACCOUNT;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		return cursor.getCount();
	}

	// ======All CRUD(Create, Read, Update, Delete) Operations DonHang=====
	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new DonHang
	public void addDonHang(DonHang donhang) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_DH_ORDERID, donhang.getOrderId());// donhang id
		values.put(KEY_DH_ADDRESS, donhang.getAddress()); // address
		values.put(KEY_DH_BUILDINGNAME, donhang.getBuildingName()); // building
																	// name
		values.put(KEY_DH_CUSTOMERNAME, donhang.getCustomerName()); // customer
																	// name
		values.put(KEY_DH_PHONE, donhang.getPhone()); // phone
		values.put(KEY_DH_ADDRESSTYPE, donhang.getAddressType()); // address
																	// type
		values.put(KEY_DH_NOTE, donhang.getNote());// note
		values.put(KEY_DH_TOTALMONEY, donhang.getTotalMoney());
		values.put(KEY_DH_TIMECREATED, donhang.getTimeCreated());
		values.put(KEY_DH_HISTORYLOG, donhang.getHistoryLog().toString());
		values.put(KEY_DH_SHIPEXPRESS, donhang.getShipExpress());
		values.put(KEY_DH_STATUS, donhang.getStatus());
		values.put(KEY_DH_STATUS_TRAVE, donhang.getStatus_trave());
		values.put(KEY_DH_ISVIP, donhang.getIsVip());
		values.put(KEY_DH_NUMBERDELAY, donhang.getNumberDelay());
		values.put(KEY_DH_IS_SYNCHONIZE, donhang.getIs_Synchonize());

		// Inserting Row
		db.insert(TABLE_DONHANG, null, values);

		db.close(); // Closing database connection
	}

	
	// Getting single DonHang
	public DonHang getDonHang(int orderId) {
		String selectQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
				+ KEY_DH_ORDERID + "=" + orderId;
		DonHang dh = new DonHang();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {

				dh.setOrderId(Integer.parseInt(cursor.getString(0)));
				dh.setAddress(cursor.getString(1));
				dh.setBuildingName(cursor.getString(2));
				dh.setCustomerName(cursor.getString(3));
				dh.setPhone(cursor.getString(4));
				dh.setAddressType(Integer.parseInt(cursor.getString(5)));
				dh.setNote(cursor.getString(6));
				dh.setTotalMoney(cursor.getDouble(7));
				dh.setTimeCreated(cursor.getString(8));
				// dh.setHistoryLog(cursor.getString(9));
				dh.setShipExpress(cursor.getInt(10));
				dh.setStatus(Integer.parseInt(cursor.getString(11)));
				dh.setStatus_trave(Integer.parseInt(cursor.getString(12)));
				dh.setIsVip(Integer.parseInt(cursor.getString(13)));
				dh.setNumberDelay(Integer.parseInt(cursor.getString(14)));
				dh.setIs_Synchonize(Integer.parseInt(cursor.getString(15)));

				// Adding donhang to list
			} while (cursor.moveToNext());
		}
		// return donhang
		return dh;
	}

	public List<DonHang> getDonHangByKeyword(String keyword) {
		List<DonHang> donhangs = new ArrayList<DonHang>();
		// Select All Query
		ConvertUnsigned convert = new ConvertUnsigned();

		WriteLog.d("Change Keyword", convert.ConvertString(keyword));

		String selectQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
				+ KEY_DH_STATUS_TRAVE + "=" + Utils.DH_STATUS_RESTORE + " AND "
				+ KEY_DH_ADDRESS + " LIKE " + "'" + "%" + keyword + "%" + "'";// status_trave=10

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				DonHang dh = new DonHang();

				dh.setOrderId(Integer.parseInt(cursor.getString(0)));
				dh.setAddress(cursor.getString(1));
				dh.setBuildingName(cursor.getString(2));
				dh.setCustomerName(cursor.getString(3));
				dh.setPhone(cursor.getString(4));
				dh.setAddressType(Integer.parseInt(cursor.getString(9)));
				dh.setNote(cursor.getString(10));
				dh.setTotalMoney(cursor.getDouble(5));
				dh.setTimeCreated(cursor.getString(6));
				// dh.setHistoryLog(cursor.getString(9));
				dh.setShipExpress(cursor.getInt(8));
				dh.setStatus(Integer.parseInt(cursor.getString(11)));
				dh.setStatus_trave(Integer.parseInt(cursor.getString(12)));
				dh.setIsVip(Integer.parseInt(cursor.getString(13)));
				dh.setNumberDelay(Integer.parseInt(cursor.getString(14)));
				dh.setIs_Synchonize(Integer.parseInt(cursor.getString(15)));
				// Adding donhang to list
				donhangs.add(dh);
			} while (cursor.moveToNext());
		}

		// return donhangs list
		return donhangs;
	}

	// Getting All DonHangs
	public List<DonHang> getAllDonHangDangGiao() {
		List<DonHang> donHangList = new ArrayList<DonHang>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
				+ KEY_DH_STATUS_TRAVE + "=" + Utils.DH_STATUS_RESTORE;// status_trave=10

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				DonHang dh = new DonHang();

				dh.setOrderId(Integer.parseInt(cursor.getString(0)));
				dh.setAddress(cursor.getString(1));
				dh.setBuildingName(cursor.getString(2));
				dh.setCustomerName(cursor.getString(3));
				dh.setPhone(cursor.getString(4));
				dh.setAddressType(Integer.parseInt(cursor.getString(9)));
				dh.setNote(cursor.getString(10));
				dh.setTotalMoney(cursor.getDouble(5));
				dh.setTimeCreated(cursor.getString(6));
				// dh.setHistoryLog(cursor.getString(9));
				dh.setShipExpress(cursor.getInt(8));
				dh.setStatus(Integer.parseInt(cursor.getString(11)));
				dh.setIsVip(Integer.parseInt(cursor.getString(13)));
				dh.setNumberDelay(Integer.parseInt(cursor.getString(14)));
				dh.setIs_Synchonize(Integer.parseInt(cursor.getString(15)));
				// Adding donhang to list
				donHangList.add(dh);
			} while (cursor.moveToNext());
		}

		// return donhangs list
		return donHangList;
	}

	// Getting All DonHangs
	public List<DonHang> getAllDonHangsTraVe(int status_trave) {// status_trave=1
		List<DonHang> donHangList = new ArrayList<DonHang>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
				+ KEY_DH_STATUS_TRAVE + "!=" + status_trave;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				DonHang dh = new DonHang();
				dh.setOrderId(Integer.parseInt(cursor.getString(0)));
				dh.setAddress(cursor.getString(1));
				dh.setBuildingName(cursor.getString(2));
				dh.setCustomerName(cursor.getString(3));
				dh.setPhone(cursor.getString(4));
				dh.setAddressType(Integer.parseInt(cursor.getString(9)));
				dh.setNote(cursor.getString(10));
				dh.setTotalMoney(cursor.getDouble(5));
				dh.setTimeCreated(cursor.getString(6));
				// dh.setHistoryLog(cursor.getString(9));
				dh.setShipExpress(cursor.getInt(8));
				dh.setStatus(Integer.parseInt(cursor.getString(11)));
				dh.setIsVip(Integer.parseInt(cursor.getString(13)));
				dh.setNumberDelay(Integer.parseInt(cursor.getString(14)));
				dh.setIs_Synchonize(Integer.parseInt(cursor.getString(15)));
				// Adding donhang to list
				donHangList.add(dh);
			} while (cursor.moveToNext());
		}

		// return donhangs list
		return donHangList;
	}
	
	
	// Getting All DonHangs Can Synchonize
		public List<DonHang> getAllDonHangsSynchonizes() {// status_trave=1
			List<DonHang> donHangList = new ArrayList<DonHang>();
			// Select All Query
			String selectQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
					+ KEY_DH_IS_SYNCHONIZE + "=" + Utils.DH_SYNCHONIZED;

			SQLiteDatabase db = this.getWritableDatabase();
			Cursor cursor = db.rawQuery(selectQuery, null);

			// looping through all rows and adding to list
			if (cursor.moveToFirst()) {
				do {
					DonHang dh = new DonHang();
					dh.setOrderId(Integer.parseInt(cursor.getString(0)));
					dh.setAddress(cursor.getString(1));
					dh.setBuildingName(cursor.getString(2));
					dh.setCustomerName(cursor.getString(3));
					dh.setPhone(cursor.getString(4));
					dh.setAddressType(Integer.parseInt(cursor.getString(9)));
					dh.setNote(cursor.getString(10));
					dh.setTotalMoney(cursor.getDouble(5));
					dh.setTimeCreated(cursor.getString(6));
					// dh.setHistoryLog(cursor.getString(9));
					dh.setShipExpress(cursor.getInt(8));
					dh.setStatus(Integer.parseInt(cursor.getString(11)));
					dh.setIsVip(Integer.parseInt(cursor.getString(13)));
					dh.setNumberDelay(Integer.parseInt(cursor.getString(14)));
					dh.setIs_Synchonize(Integer.parseInt(cursor.getString(15)));
					// Adding donhang to list
					donHangList.add(dh);
				} while (cursor.moveToNext());
			}

			// return donhangs list
			return donHangList;
		}

	// Updating single donhang
	public int updateDonhang(DonHang donhang) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_DH_ORDERID, donhang.getOrderId());// donhang id
		values.put(KEY_DH_ADDRESS, donhang.getAddress()); // address
		values.put(KEY_DH_BUILDINGNAME, donhang.getBuildingName()); // building
																	// name
		values.put(KEY_DH_CUSTOMERNAME, donhang.getCustomerName()); // customer
																	// name
		values.put(KEY_DH_PHONE, donhang.getPhone()); // phone
		values.put(KEY_DH_ADDRESSTYPE, donhang.getAddressType()); // address
																	// type
		values.put(KEY_DH_NOTE, donhang.getNote());// note

		values.put(KEY_DH_TOTALMONEY, donhang.getTotalMoney());
		values.put(KEY_DH_TIMECREATED, donhang.getTimeCreated());
		// values.put(KEY_DH_HISTORYLOG, donhang.getHistoryLog().toString());
		values.put(KEY_DH_SHIPEXPRESS, donhang.getShipExpress());

		values.put(KEY_DH_STATUS, donhang.getStatus());// status donhang
		values.put(KEY_DH_STATUS_TRAVE, donhang.getStatus_trave());// status
																	// trave
																	// donhang
		values.put(KEY_DH_IS_SYNCHONIZE, donhang.getIs_Synchonize());
		// updating row
		return db.update(TABLE_DONHANG, values, KEY_DH_ORDERID + " = ?",
				new String[] { String.valueOf(donhang.getOrderId()) });
	}

	// Updating single donhang
		public int updateSynchonizeDonhang(int orderId,int statusSynchonize) {
			SQLiteDatabase db = this.getWritableDatabase();

			ContentValues values = new ContentValues();
																		// trave
																		// donhang
			values.put(KEY_DH_IS_SYNCHONIZE, statusSynchonize);
			// updating row
			return db.update(TABLE_DONHANG, values, KEY_DH_ORDERID + " = ?",
					new String[] { String.valueOf(orderId) });
		}
		
	public int updateTotalMoney(int orderId, double totalMoney) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_DH_TOTALMONEY, totalMoney);

		// updating row
		return db.update(TABLE_DONHANG, values, KEY_DH_ORDERID + " = ?",
				new String[] { String.valueOf(orderId) });
	}

	public int updateDonHangNumberDelay(int orderId, int numberDelay) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_DH_NUMBERDELAY, numberDelay);

		// updating row
		return db.update(TABLE_DONHANG, values, KEY_DH_ORDERID + " = ?",
				new String[] { String.valueOf(orderId) });
	}

	// Deleting single Donhang
	public void deleteDonhang(DonHang dh) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_DONHANG, KEY_DH_ORDERID + " = ?",
				new String[] { String.valueOf(dh.getOrderId()) });
		db.close();
	}

	// Getting DonHangs Count
	public int getDonHangCount() {
		String countQuery = "SELECT  * FROM " + TABLE_DONHANG + " where "
				+ KEY_DH_STATUS_TRAVE + Utils.DH_STATUS_RESTORE;// status_trave=10;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();
		// return count
		return cursor.getCount();
	}

	// ======All CRUD(Create, Read, Update, Delete) Operations SKUS =====

	// Adding new Sku
	public void addSku(int orderId, SKU sku) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		WriteLog.d("SKU_ID:", sku.getSkuId() + "Order_ID:" + orderId);
		values.put(KEY_SKU_SKUID, sku.getSkuId());
		values.put(KEY_SKU_ORDERID, orderId);
		values.put(KEY_SKU_SKUNAME, sku.getSkuName());
		values.put(KEY_SKU_PRICE, sku.getPrice());
		values.put(KEY_SKU_QUANTITY, sku.getQuantity());
		values.put(KEY_SKU_PRODUCTTYPE, sku.getProductType()); // product type
		values.put(KEY_SKU_QUANTITY_DELIVERY, sku.getQuantity());

		// Inserting Row
		db.insert(TABLE_SKU, null, values);
		db.close(); // Closing database connection
	}

	// Getting All Skus
	public List<SKU> getAllSKUByOrderId(int orderId) {
		List<SKU> skuList = new ArrayList<SKU>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_SKU + " where "
				+ KEY_SKU_ORDERID + "=" + orderId;// 156235

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				SKU sku = new SKU();
				sku.setOrderId(Integer.parseInt(cursor.getString(1)));
				sku.setSkuId(cursor.getInt(2));
				sku.setSkuName(cursor.getString(3));
				sku.setPrice(cursor.getDouble(4));
				sku.setQuantity(cursor.getInt(5));
				sku.setProductType(cursor.getInt(6));
				sku.setSku_status(cursor.getInt(7));
				sku.setSku_Quantity_Delivery(cursor.getInt(8));
				sku.setSku_why_change(cursor.getInt(9));
				// Adding sku to list
				skuList.add(sku);
			} while (cursor.moveToNext());
		}

		// return skus list
		return skuList;
	}

	public List<SKU> getAllReportSkus() {
		List<SKU> skuList = new ArrayList<SKU>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_SKU + " where "
				+ KEY_SKU_STATUS + Utils.SKU_STATUS_SUCCESS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				SKU sku = new SKU();
				sku.setOrderId(Integer.parseInt(cursor.getString(1)));
				sku.setSkuId(cursor.getInt(2));
				sku.setSkuName(cursor.getString(3));
				sku.setPrice(cursor.getDouble(4));
				sku.setQuantity(cursor.getInt(5));
				sku.setProductType(cursor.getInt(6));
				sku.setSku_status(cursor.getInt(7));
				sku.setSku_Quantity_Delivery(cursor.getInt(8));
				sku.setSku_why_change(cursor.getInt(9));
				// Adding sku to list
				skuList.add(sku);
			} while (cursor.moveToNext());
		}

		// return skus list
		return skuList;
	}

	public List<SKU> getReportSkus(int product_type) {
		List<SKU> skuList = new ArrayList<SKU>();
		// Select All Query
		String selectQueryAllReports = "SELECT  * FROM " + TABLE_SKU
				+ " where " + KEY_SKU_STATUS + "=" + Utils.SKU_STATUS_SUCCESS ;

		String selectQuery = "SELECT  * FROM " + TABLE_SKU + " where "
				+ KEY_SKU_STATUS + "=" + Utils.SKU_STATUS_SUCCESS + "  and "
				+ KEY_SKU_PRODUCTTYPE + "=" + product_type;

		String query = product_type > 0 ? selectQuery : selectQueryAllReports;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				SKU sku = new SKU();
				sku.setOrderId(Integer.parseInt(cursor.getString(1)));
				sku.setSkuId(cursor.getInt(2));
				sku.setSkuName(cursor.getString(3));
				sku.setPrice(cursor.getDouble(4));
				sku.setQuantity(cursor.getInt(5));
				sku.setProductType(cursor.getInt(6));
				sku.setSku_status(cursor.getInt(7));
				sku.setSku_Quantity_Delivery(cursor.getInt(8));
				sku.setSku_why_change(cursor.getInt(9));
				// Adding sku to list
				skuList.add(sku);
			} while (cursor.moveToNext());
		}

		// return skus list
		return skuList;
	}

	// Updating single Sku
	public int updateSku(int status, int quantity_delivery, int orderId,
			int skuId) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_SKU_QUANTITY_DELIVERY, quantity_delivery);
		
		values.put(KEY_SKU_STATUS, status);
		// updating row
		int row = db
				.update(TABLE_SKU,
						values,
						KEY_SKU_SKUID + " = ?" + " AND " + KEY_SKU_ORDERID
								+ " = ?",
						new String[] { String.valueOf(skuId),
								String.valueOf(orderId) });
		WriteLog.d("row updated:", row + "");
		return row;
	}

	// Updating single Sku
	public int updateSkuWhyChange(int why_change, int orderId, int skuId) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_SKU_WHY_CHANGE, why_change);
		// updating row
		int row = db
				.update(TABLE_SKU,
						values,
						KEY_SKU_SKUID + " = ?" + " AND " + KEY_SKU_ORDERID
								+ " = ?",
						new String[] { String.valueOf(skuId),
								String.valueOf(orderId) });
		WriteLog.d("row why_change updated:", row + "");
		return row;
	}
}
